# 数据库访问层
import sqlite3

DB_FILE = "db/database.sqlite3"


# 初始化数据库
def init_db():
    conn = None

    try:
        # 创建数据库连接
        conn = sqlite3.connect(DB_FILE)

        sql = """
            CREATE TABLE users (
            user_id INT PRIMARY KEY,
            username VARCHAR(50) NOT NULL,
            email VARCHAR(100) NOT NULL,
            phone VARCHAR(20)
            )
        """
        # 执行SQL语句
        conn.execute(sql)
        print("数据库操作成功")
    except:
        print("数据库操作失败")
    finally:
        # 关闭数据库连接
        if conn:
            conn.close()


# 查询所有数据
def find_all():
    datalist = []
    sql = "select user_id,username,email,phone from users"

    try:
        # 创建数据库连接
        conn = sqlite3.connect(DB_FILE)
        # 创建游标对象
        cur = conn.cursor()
        # 执行SQL语句
        cur.execute(sql)
        # 提取结果集
        result_set = cur.fetchall()
        # 遍历结果集
        for row in result_set:
            dict = {}
            dict["user_id"] = row[0]
            dict["username"] = row[1]
            dict["email"] = row[2]
            dict["phone"] = row[3]
            datalist.append(dict)
    except Exception as e:
        print("数据库操作失败", e)
    finally:
        # 关闭数据库连接
        if conn:
            conn.close()

    return datalist


# 删除数据
def delete(userid):
    sql = "delete  from users where user_id=?"

    try:
        # 创建数据库连接
        conn = sqlite3.connect(DB_FILE)
        # 创建游标对象
        cur = conn.cursor()
        # 执行SQL语句
        afcount = cur.execute(sql, [userid])
        print("成功删除", afcount)
        # 提交事务
        conn.commit()
        return 1  # 1表示删除成功
    except Exception as e:
        print("数据库操作失败", e)
        # 回滚事务
        conn.rollback()
    finally:
        # 关闭数据库连接
        if conn:
            conn.close()

    return -1  # -1表示删除失败


# 插入数据
def create(user):
    sql = "INSERT INTO users (user_id,username,email,phone) VALUES (?,?,?,?)"

    try:
        # 创建数据库连接
        conn = sqlite3.connect(DB_FILE)
        # 创建游标对象
        cur = conn.cursor()
        # 执行SQL语句
        # 参数列表
        prams = [user["user_id"], user["username"], user["email"], user["phone"]]
        afcount = cur.execute(sql, prams)  # afcount是成功插入的数据个数
        print("成功插入", afcount)
        # 提交事务
        conn.commit()
        return 1  # 1表示插入成功
    except Exception as e:
        print("数据库操作失败", e)
        # 回滚事务
        conn.rollback()
    finally:
        # 关闭数据库连接
        if conn:
            conn.close()

    return -1  # -1表示插入失败


# 删除数据
def delete(userid):
    sql = "delete  from users where user_id=?"

    try:
        # 创建数据库连接
        conn = sqlite3.connect(DB_FILE)
        # 创建游标对象
        cur = conn.cursor()
        # 执行SQL语句
        afcount = cur.execute(sql, [userid])
        print("成功删除", afcount)
        # 提交事务
        conn.commit()
        return 1  # 1表示删除成功
    except Exception as e:
        print("数据库操作失败", e)
        # 回滚事务
        conn.rollback()
    finally:
        # 关闭数据库连接
        if conn:
            conn.close()

    return -1  # -1表示删除失败


# 更新数据
def update(user):
    sql = "UPDATE users SET username=?,email=?,phone=? WHERE user_id=?"

    try:
        # 创建数据库连接
        conn = sqlite3.connect(DB_FILE)
        # 创建游标对象
        cur = conn.cursor()
        # 执行SQL语句
        # 参数列表
        prams =[ user["username"], user["email"], user["phone"], user["user_id"]]
        afcount = cur.execute(sql, prams)
        print("成功更新", afcount)
        # 提交事务
        conn.commit()
        return 1  # 1表示更新成功
    except Exception as e:
        print("数据库操作失败", e)
        # 回滚事务
        conn.rollback()
    finally:
        # 关闭数据库连接
        if conn:
            conn.close()

    return -1  # -1表示更新失败


# 根据用户ID查询数据
def find_by_id(userid):
    dict = {}
    sql = "select user_id,username,email,phone from users where user_id=?"

    try:
        # 创建数据库连接
        conn = sqlite3.connect(DB_FILE)
        # 创建游标对象
        cur = conn.cursor()
        # 执行SQL语句
        cur.execute(sql, [userid])
        # 提取结果集
        result_set = cur.fetchone()
        if result_set:
            dict["user_id"] = result_set[0]
            dict["username"] = result_set[1]
            dict["email"] = result_set[2]
            dict["phone"] = result_set[3]
    except Exception as e:
        print("数据库操作失败", e)
    finally:
        # 关闭数据库连接
        if conn:
            conn.close()
    return dict
